package com.shoujifeng.snowmusic.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.shoujifeng.snowmusic.entity.ItemRecord;

import android.app.ProgressDialog;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DatabaseUtil {
	private DatabaseHelper helper;
	public static final String lock = "访问";

	public DatabaseUtil(Context context) {
		helper = new DatabaseHelper(context, "downloadFile.db", null, 1);
	}

	// 添加记录
	public void insert(String path, int threadid, int downloadlength) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(
				"INSERT INTO info(path,threadid,downloadlength) VALUES (?,?,?)",
				new Object[] { path, threadid, downloadlength });
		//print("成功添加");
		db.close();
	}

	// 删除记录
	public void delete(String path, int threadid) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DELETE FROM info WHERE path = ? AND threadid = ?",
				new Object[] { path, threadid });
		//print("成功删除");
		db.close();
	}

	// 更新记录
	public void update(String path, int threadid, int downloadlength) {
		SQLiteDatabase db = helper.getWritableDatabase();
		// print("准备更新1");
		db.execSQL(
				"UPDATE info SET downloadlength = ? WHERE path = ? AND threadid = ?",
				new Object[] { downloadlength, path, threadid });
		// print("成功更新2");
		db.close();
	}

	// 查询线程是否存在
	public ItemRecord query(String path, int threadid) {
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor c = db
				.rawQuery(
						"SELECT path,threadid,downloadlength FROM info WHERE path = ? AND threadid = ?",
						new String[] { path, Integer.toString(threadid) });
		ItemRecord record = null;
		if (c.moveToNext()) {
			record = new ItemRecord(c.getString(0), c.getInt(1), c.getInt(2));
		}
		c.close();
		db.close();
		return record;
	}

	// 查询未完成任务
	public List<String> query(String path) {
		//print("List<String> query 开始");
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor c = db.rawQuery("SELECT DISTINCT path FROM info",
				new String[] { path });
		List<String> arrayList = new ArrayList<String>();
		while (c.moveToNext()) {
			arrayList.add(c.getString(0));
		}
		c.close();
		db.close();
		//print("List<String> query 结束");
		return arrayList;
	}

	// 添加所有的歌曲到表中
	public void insertMusic(String musicName, String playerName, long duration,
			long size, String url, String album) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(
				"INSERT INTO MusicList(musicName,playerName,duration,size,url,album) VALUES (?,?,?,?,?,?)",
				new Object[] { musicName, playerName, duration, size, url,
						album });
		//print("成功添加");
		db.close();
	}

	//清空收藏
			public void clearMusicList()
			{
				SQLiteDatabase db = helper.getWritableDatabase();
				
				db.execSQL("DELETE FROM MusicList",
						new Object[] { });
				
				db.close();
			}
			
	// 添加歌单记录
	public void insertMenu(String name, String musicName, String playerName,
			long duration, long size, String url) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(
				"INSERT INTO SongMenu(name,musicName,playerName,duration,size,url) VALUES (?,?,?,?,?,?)",
				new Object[] { name, musicName, playerName, duration, size, url });
		//print("成功添加");
		db.close();
	}

	// 查询歌单
	public List<HashMap<String, Object>> queryMusicMenu() {
		//print("List<String> query 开始");
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor c = db.rawQuery(
				"SELECT *  FROM SongMenu where name != ? group by name ",
				new String[] { "我喜欢的歌曲" });
		List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
		int count = c.getCount();
		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("name", c.getString(c.getColumnIndex("name")));
			map.put("musicName", c.getString(c.getColumnIndex("musicName")));
			map.put("playerName", c.getString(c.getColumnIndex("playerName")));
			arrayList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		//print("List<String> query 结束");
		return arrayList;
	}

	// 查询歌手列表
	public List<HashMap<String, String>> queryPlayerName() {
		List<HashMap<String, String>> playerList = new ArrayList<HashMap<String, String>>();
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor c = db.rawQuery(
				"SELECT playerName  FROM MusicList group by playerName ",
				new String[] {});
		int count = c.getCount();
		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("playerName", c.getString(c.getColumnIndex("playerName")));
			playerList.add(map);
			c.moveToNext();
		}
		c.close();
		db.close();
		//print("List<String> query 结束");
		return playerList;
	}

	// 查询某个歌手的所有音乐
	public List<HashMap<String, Object>> queryMusicByPlayer(String playerName) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "SELECT *  FROM MusicList where playerName = ? group by musicName";
		Cursor c = db.rawQuery(sql, new String[] { playerName });
		List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
		int count = c.getCount();

		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, Object> map = new HashMap<String, Object>();

			map.put("title", c.getString(c.getColumnIndex("musicName")));
			map.put("Artist", c.getString(c.getColumnIndex("playerName")));
			map.put("url", c.getString(c.getColumnIndex("url")));
			map.put("duration", c.getLong(c.getColumnIndex("duration")));
			map.put("size", c.getLong(c.getColumnIndex("size")));
			map.put("album", c.getString(c.getColumnIndex("album")));

			arrayList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		//print("List<String> query 结束");
		return arrayList;
	}

	// 查询专辑列表
	public List<HashMap<String, String>> queryAlbumName() {
		List<HashMap<String, String>> playerList = new ArrayList<HashMap<String, String>>();
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor c = db.rawQuery(
				"SELECT album ,playerName FROM MusicList group by album ",
				new String[] {});
		int count = c.getCount();
		c.moveToFirst();

		for (int i = 0; i < count; i++) {
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("spceialName", c.getString(c.getColumnIndex("album")));
			map.put("playerName", c.getString(c.getColumnIndex("playerName")));
			playerList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		//print("List<String> query 结束");
		return playerList;
	}

	// 获取某个专辑中的所有音乐
	public List<HashMap<String, Object>> queryMusicBySpecial(String specialName) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "SELECT *  FROM MusicList where album = ? group by musicName";
		Cursor c = db.rawQuery(sql, new String[] { specialName });
		List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
		int count = c.getCount();

		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, Object> map = new HashMap<String, Object>();

			map.put("title", c.getString(c.getColumnIndex("musicName")));
			map.put("Artist", c.getString(c.getColumnIndex("playerName")));
			map.put("url", c.getString(c.getColumnIndex("url")));
			map.put("duration", c.getLong(c.getColumnIndex("duration")));
			map.put("size", c.getLong(c.getColumnIndex("size")));
			map.put("album", c.getString(c.getColumnIndex("album")));

			arrayList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		//print("List<String> query 结束");
		return arrayList;
	}

	// 删除歌曲

	// 删除记录
	public void deleteMusic(String url) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DELETE FROM MusicList WHERE url = ? ", new String[] { url });
		//print("成功删除");
	}

	// 获取所有音乐
	public List<HashMap<String, Object>> queryAllMusic() {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "SELECT *  FROM MusicList ";
		Cursor c = db.rawQuery(sql, new String[] {});
		List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
		int count = c.getCount();

		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, Object> map = new HashMap<String, Object>();

			map.put("musicName", c.getString(c.getColumnIndex("musicName")));
			map.put("playerName", c.getString(c.getColumnIndex("playerName")));
			map.put("url", c.getString(c.getColumnIndex("url")));
			map.put("duration", c.getLong(c.getColumnIndex("duration")));
			map.put("size", c.getLong(c.getColumnIndex("size")));
			map.put("album", c.getString(c.getColumnIndex("album")));

			arrayList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		//print("List<String> query 结束");
		return arrayList;
	}

	// 查询歌单中的音乐列表
	public List<HashMap<String, Object>> queryMusicMenuList(String name) {
		//print("List<String> query 开始");
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "SELECT *  FROM SongMenu where name = ? and musicName!= '' group by musicName";
		Cursor c = db.rawQuery(sql, new String[] { name });
		List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
		int count = c.getCount();
		c.moveToFirst();
		for (int i = 0; i < count; i++) {
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("name", c.getString(c.getColumnIndex("name")));
			map.put("musicName", c.getString(c.getColumnIndex("musicName")));
			map.put("playerName", c.getString(c.getColumnIndex("playerName")));
			map.put("url", c.getString(c.getColumnIndex("url")));
			map.put("duration", c.getLong(c.getColumnIndex("duration")));
			map.put("size", c.getLong(c.getColumnIndex("size")));
			map.put("index", c.getLong(c.getColumnIndex("id")));
			arrayList.add(map);
			c.moveToNext();
		}

		c.close();
		db.close();
		return arrayList;
	}
	
	// 查询歌单中的音乐列表
		public int getDownloadSize() {
			//print("List<String> query 开始");
			SQLiteDatabase db = helper.getWritableDatabase();
			String sql = "SELECT count(*) as count  FROM Download where over = ?";
			Cursor c = db.rawQuery(sql, new String[] { "1" });
			List<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
			int count = c.getCount();
			
			int size = 0;
			c.moveToFirst();
			for (int i = 0; i < count; i++) {
				size = c.getInt(c.getColumnIndex("count"));
			}
			
			c.close();
			db.close();
			return size;
		}

	// 删除记录
	public void deleteMusicMenu(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DELETE FROM SongMenu WHERE name = ? ",
				new String[] { name });
		//print("成功删除");
	}
	
	// 删除记录
		public void deleteMusicMenu(int id) {
			SQLiteDatabase db = helper.getWritableDatabase();
			db.execSQL("DELETE FROM SongMenu WHERE id = ? ",
					new Object[] { id });
			//print("成功删除");
		}

	// 更新记录
	public void updateMusicMenu(String name, String lastName) {
		SQLiteDatabase db = helper.getWritableDatabase();
		// print("准备更新1");
		db.execSQL("UPDATE SongMenu SET name = ? WHERE name = ? ",
				new Object[] { name, lastName });
		// print("成功更新2");
		db.close();
	}
	
	//插入收藏
	public void insertConllect(int index, long uid) {
		SQLiteDatabase db = helper.getWritableDatabase();
		// print("准备更新1");
		db.execSQL("INSERT INTO Conllect(idx, uid) Values(?,?) ",
				new Object[] { index, uid });
		
		// print("成功更新2");
		db.close();
	}
	
	//删除收藏
		public void deleteConllect(int index) {
			SQLiteDatabase db = helper.getWritableDatabase();
			
			db.execSQL("DELETE FROM Conllect Where idx = ? ",
					new Object[] { index });
			
			db.close();
		}
		
		//清空收藏
		public void clearConllect()
		{
			SQLiteDatabase db = helper.getWritableDatabase();
			
			db.execSQL("DELETE FROM Conllect",
					new Object[] { });
			
			db.close();
		}
		
		//查询是否存在该收藏
		public Boolean haveConllect(int index)
		{
			Boolean bRet = false;
			SQLiteDatabase db = helper.getWritableDatabase();
			String sql = "SELECT *  FROM Conllect where idx = ?";
			Cursor c = db.rawQuery(sql, new String[] { index+"" });
			
			c.moveToFirst();
			if(c.getCount() > 0)
				bRet = true;
			
			c.close();
			db.close();

			return bRet;
		}
		
		//获取收藏数量
		public int getConllectSize() {
			
			int size = 0;
			
			try {
				SQLiteDatabase db = helper.getWritableDatabase();
				String sql = "SELECT count(*) as count  FROM Conllect";
				Cursor c = db.rawQuery(sql, new String[] {});
				
				int count = c.getCount();
				
				
				c.moveToFirst();
				for (int i = 0; i < count; i++) {
					size = c.getInt(c.getColumnIndex("count"));
				}
				
				c.close();
				db.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			//print("List<String> query 开始");
			
			return size;
		}

		//-----------------------------下载管理---------------------------
		
		//插入下载
		public void insertDownload(int uid, String name, String singer, 
				String icon_url, String file_url, String url) {
			
			try {
				SQLiteDatabase db = helper.getWritableDatabase();
				db.execSQL("INSERT INTO Download(uid, name, singer, icon_url, file_url, url, lenght, clenght, over) " +
						"values(?, ?, ?,?, ?, ?, 0, 0, 0)", 
						new Object[] { uid, name, singer, icon_url, file_url, url});
				
				db.close();
			} catch (Exception e) {
				String errorString = e.getMessage();
			}
			
		}
		
		//更新下载
		public void updateDownload(int uid, String length,  String clenght, String path, int over) {
			
			SQLiteDatabase db = helper.getWritableDatabase();
			db.execSQL("UPDATE Download SET file_url = ? ,lenght = ? , clenght = ?, over = ? Where uid = ?", 
					new Object[] { path, length, clenght, over, uid});
			
			db.close();
			
		}
		
		//删除下载
		public void deleteDown(String name) {
			SQLiteDatabase db = helper.getWritableDatabase();
					
			db.execSQL("DELETE FROM Download Where name = ? ",
							new Object[] { name });
					
			db.close();
				}
		
	// 删除下载
	public void deleteDown(int uid) {
		SQLiteDatabase db = helper.getWritableDatabase();

		db.execSQL("DELETE FROM Download Where uid = ? ", new Object[] { uid });

		db.close();
	}
		
		//获取下载数据
		public ArrayList<HashMap<String, Object>> getDownload(int page, int over) {
			
			int s = page * 20;
			SQLiteDatabase db = helper.getWritableDatabase();
			String sql = "SELECT * FROM Download WHERE over = ? ";
			
			String[] selectString = null;
			if(page != -1) {
				sql = sql + " LIMIT ?, 20";
				selectString = new String[] { over + "", s+"" };
			}
			else {
				selectString = new String[] { over + ""};
			}
			Cursor c = db.rawQuery(sql, selectString);
			
			ArrayList<HashMap<String, Object>> arrayList = new ArrayList<HashMap<String, Object>>();
			
			int count = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < count; i++) {
				HashMap<String, Object> map = new HashMap<String, Object>();
				map.put("uid", c.getInt(c.getColumnIndex("uid")));
				map.put("name", c.getString(c.getColumnIndex("name")));
				map.put("singer", c.getString(c.getColumnIndex("singer")));
				map.put("icon_url", c.getString(c.getColumnIndex("icon_url")));
				map.put("file_url", c.getString(c.getColumnIndex("file_url")));
				map.put("url", c.getString(c.getColumnIndex("url")));
				map.put("pro", c.getString(c.getColumnIndex("lenght")));
				map.put("pro_max", c.getString(c.getColumnIndex("clenght")));
				arrayList.add(map);
				c.moveToNext();
			}

			c.close();
			db.close();
			return arrayList;
		}
		
		
		//查询下载是否存在
	public Boolean haveDownload(int index) {
		
		Boolean bRet = false;
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "SELECT *  FROM Download where uid = ?";
		Cursor c = db.rawQuery(sql, new String[] { index + "" });

		c.moveToFirst();
		if (c.getCount() > 0)
			bRet = true;

		c.close();
		db.close();

		return bRet;
	}
				
	// 调试信息输出
	private void print(String msg) {
		Log.d("DatabaseUtil", msg);
	}
}
